<?php
/*
 * @Author: 李明
 * @Date: 2021-09-10 14:01:06
 * @LastEditTime: 2021-10-20 12:13:18
 * @LastEditors: 李明
 * @Description:
 * @FilePath: /CRM2.0-PHP/app/Http/Controllers/file/ExcelController.php
 * @山东壹零贰肆信息科技有限公司
 */


namespace App\Http\Controllers\file;

use App\Http\Controllers\common\CommonFun;
use App\Http\Controllers\common\LMReturn;
use App\Http\Controllers\Controller;

// use App\Http\Controllers\order\OrderController;
use App\Http\Controllers\script\UpdateScript;
use App\Models\Branch;
use App\Models\Order;
use App\Models\Product;
use App\Models\User;
use App\Models\Worker;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExcelController extends Controller
{
    /**
     * @description: 获取表格数据
     * @param {Request} $request
     * @return {*}
     * 注意 处理过时间格式
     */
    public function importToExport(Request $request)
    {
        //去验证token值
        $tokenInfo = json_decode($this->verifyTokenLm(), true);
        //判断传递过来的token的code码是否等于400  如果是400的话，提示token不正确
        if ($tokenInfo['code'] == 400) {
            return LMReturn::tokenErr($tokenInfo['msg'], $tokenInfo['code']);
        }
        //判断文件格式
        $file = $request->file('excel');
        if (empty($file)) {
            return back()->withInput()->with('questionError', '请上传需转换文件');
        }

        if (!in_array($file->getClientOriginalExtension(), ['xls', 'xlsx'])) {

            return back()->withErrors(['import' => '文件仅支持xls、xlsx'])->withInput();
        }

        //获取文件
        $fileName = $tokenInfo['worker_id'] . time() . '.' . $file->getClientOriginalExtension();
        $filePath = public_path('excel/' . date("Ymd") . '/');
        $file->move($filePath, $fileName);
        //phpExcel初始化
        $reader = IOFactory::createReader('Xlsx'); //设置以Excel5格式(Excel97-2003工作簿)
        $phpExcel = $reader->load($filePath . $fileName); //载入excel文件
        $excelSheet = $phpExcel->getSheet(0); //读取第一个工作表

        $highestRow = $excelSheet->getHighestRow(); //取得总行数
        $highestColumn = $excelSheet->getHighestColumn(); //取得总列数
        //获取单元格数据

        $recordData = []; //行号从1开始

        for ($row = 1; $row <= $highestRow; $row++) {
            //列数是以A列开始
            for ($col = 'A'; $col <= $highestColumn; $col++) {
                $cellValue = $excelSheet->getCell($col . $row)->getCalculatedValue();
                if (is_object($cellValue)) {
                    $cell = $cellValue->__toString();
                };
                switch ($col) {
                    case 'A':
                        $key = 'num';
                        break;
                    case 'B':
                        $key = 'branch_name';
                        break;
                    case 'C':
                        $key = 'user_name';
                        break;
                    case 'D':
                        $key = 'order_money';
                        break;
                    case 'E':
                        $key = 'valid_time';
                        break;
                    case 'F':
                        $key = 'contract_end_date';
                        break;
                    case 'G':
                        $key = 'long_time';
                        break;
                    case 'H':
                        $key = 'pay_day';
                        break;
                    case 'I':
                        $key = 'pay_money';
                        break;
                    case 'J':
                        $key = 'results_money';
                        break;
                    case 'K':
                        $key = 'borrower_name';
                        break;
                    case 'L':
                        $key = 'order_remark';
                        break;
                    case 'M':
                        $key = 'order_status';
                        break;
                    case 'N':
                        $key = 'exit_time';
                        break;
                    case 'O':
                        $key = 'true_results';
                        break;
                    case 'P':
                        $key = 'advance_name';
                        break;
                    case 'Q':
                        $key = 'rate';
                        break;
                    case 'R':
                        $key = 'pay_type';
                        break;
                    case 'S':
                        $key = 'worker_name';
                        break;
                    case 'T':
                        $key = 'contract_no';
                        break;
                    default:
                        $key = $col;
                        break;
                }
                if (($key == 'valid_time' || $key == 'exit_time' || $key == 'contract_end_date') && $row != 1) {
                    $toTimestamp = Date::excelToTimestamp($cellValue);
                    $res_data = date('Y-m-d', $toTimestamp) == '1970-01-01' ? '' : date('Y-m-d', $toTimestamp);
                } else {
                    $res_data = $this->handleStr($cellValue);
                }
                $rowData[$key] = $res_data;
            }
            $recordData[] = $rowData;
        }
        // $return_data['values'] = $recordData;
        // $return_data['values']
        return $recordData;
    }

    public function excelImportOne(Request $request)
    {
        //去验证token值
        $tokenInfo = json_decode($this->verifyTokenLm(), true);
        //判断传递过来的token的code码是否等于400  如果是400的话，提示token不正确
        if ($tokenInfo['code'] == 400) {
            return LMReturn::tokenErr($tokenInfo['msg'], $tokenInfo['code']);
        }
        DB::beginTransaction(); //开启事务
        // 接参
        try {
            $all = $request->all();

            $num = $all['num'];
            $pay_day = $all['pay_day'];
            $pay_money = $all['pay_money'];
            $valid_time = $all['valid_time'] == '1970-01-01' ? '' : $all['valid_time'];
            $borrower_name = $all['borrower_name'];
            // $borrower_list = Borrower::where('name', $borrower_name)->get();
            $contract_no = $all['contract_no'];
            if ($contract_no == '') {
                $order_count = 0;
            } else {
                $order_list = Order::where('contract_no', $contract_no)->get();
                $order_count = count($order_list);
            }
            if ($order_count > 1) {
                return LMReturn::Error('订单数据存在问题:' . $order_count, 'EC011');
            } elseif ($order_count == 1) { // 如果是 变更借款人，在这里直接处理完毕
                $order = $order_list->first();
                $order->save();
                DB::commit(); //提交至数据库
                return LMReturn::Success($order, '变更历史借款人成功');
            } else {
                $order = new Order();
                $branch_name = $all['branch_name'];
                $user_name = $all['user_name'];
                $order_money = $all['order_money'] * 10000;
                $contract_end_date = $all['contract_end_date'] == '1970-01-01' ? '' : $all['contract_end_date'];
                $long_time = $all['long_time'];
                $results_money = $all['results_money'] * 10000;
                $order_remark = $all['order_remark'];
                $order_status_str = $all['order_status'];
                $order_status_data = $this->orderStrToStatus($order_status_str);
                $order_status = $order_status_data['order_status'];
                $cash_status = $order_status_data['cash_status'];
                $un_zip_status = $order_status_data['un_zip_status'];
                if ($order_status == $order_status_str && $contract_no == '') {
                    return LMReturn::Error('无法识别订单状态', 'EC010');
                }
                $exit_time = $all['exit_time'] == '1970-01-01' ? '' : $all['exit_time'];
                $true_results = $all['true_results'];
                $advance_name = $all['advance_name'];
                $rate = $all['rate'];
                $pay_type_str = $all['pay_type'];
                if ($pay_type_str == '按月付息') {
                    $pay_type = '2';
                } elseif ($pay_type_str == '到期本息') {
                    $pay_type = '4';
                } else {
                    $pay_type = '2';
                }
                $proWhere[] = ['pay_type', $pay_type];
                $proWhere[] = ['long_time', $long_time];
                $proWhere[] = ['rate', $rate];
                $worker_name = $all['worker_name'];
                $branch_list = Branch::where('name', $branch_name)->get();
                $user_list = User::where('name', $user_name)->get();
                $product_list = Product::where($proWhere)->get();
                $worker_list = Worker::where('name', $worker_name)->get();
            }
        } catch (\Throwable $th) {
            DB::rollback(); //数据库回滚
            return LMReturn::Error('传参出现问题', 'EC001');
        }
        // 处理参数
        try {
            $record_worker = Worker::find($tokenInfo['worker_id']);
            if (count($branch_list) == 1) { // 部门
                $branch = $branch_list->first();
            } elseif (count($branch_list) > 1) {
                return LMReturn::Error('无法确认订单部门', 'EC002');
            } else {
                $branch_pid = '3';
                $branch = new Branch();
                $branch->name = $branch_name;
                $branch->pid = $branch_pid;
                $branch->save();
                $branch->refresh();
            }
            if (count($worker_list) == 1) { // 客户经理
                $worker = $worker_list->first();
            } elseif (count($worker_list) > 1) {
                return LMReturn::Error('无法确认客户经理', 'EC007');
            } else {
                $worker = new Worker();
                $worker->name = $worker_name;
                $worker->phoneNum = $worker_name;
                $worker->password = CommonFun::md5('666666');
                $worker->save();
                $worker->branches()->sync([$branch->id]); // 替换之前的关联部门。
                $worker->refresh();
            }
            if (count($user_list) == 1) { // 用户
                $user = $user_list->first();
                // $is_first = OrderController::isFirstOrder($user);
            } elseif (count($user_list) > 1) {
                return LMReturn::Error('无法确认出借人', 'EC003');
            } else {
                $user = new User();
                $user->name = $user_name;
                $user->worker_id = $worker->id;
                $user->branch_id = $branch->id;
                // 根据 身份证号，去判断用户是否为首单/新用户  1是 2否
                // $is_first = OrderController::isFirstOrder($user);
                // $user->is_first = $is_first;
                $user->save();
                $user->refresh();
            }

            if (count($product_list) == 1) {
                $product = $product_list->first();
            } else {
                if ($order_count == 0) {
                    return LMReturn::Error('无法确认产品' . count($product_list) . '-' . $order_count . '-' . $contract_no, 'EC006');
                }
            }
        } catch (\Throwable $th) {
            DB::rollback(); //数据库回滚
            return LMReturn::Error('数据处理出现问题', 'EC008');
        }

        try {
            $order->branch_id = $branch->id;
            $order->worker_id = $worker->id;
            $order->user_id = $user->id;
            $order->record_worker_id = $record_worker->id;
            $order->product_id = $product->id;
            $order->sign_time = $valid_time;
            $order->valid_time = $valid_time;
            $order->order_money = $order_money;
            $order->contract_end_date = $contract_end_date;
            $order->order_no = $user->id . time();
            $order->results_money = $results_money;
            $order->true_results = $true_results;
            $order->remark = $order_remark;
            $order->exit_time = $exit_time;
            $order->cash_time = $exit_time;
            $order->order_status = $order_status;
            // $order->is_first = $is_first;
            $order->contract_no = $contract_no;
            $order->audit_step = '3'; // 审核步数 1初审 2复审 3正常订单
            $order->cash_status = $cash_status; // 根据订单状态返回 兑付状态
            $order->un_zip_status = $un_zip_status; // 根据订单状态返回 解押状态
            $order->save();
            //添加利息记录
            UpdateScript::createOrderInterest($order);
            //根据兑付时间，判断是否存在提前兑付的情况，如果提前兑付，需要修改利息记录还款状态
            $order->is_exit = $exit_time < $contract_end_date ? '1' : '2';
            DB::commit(); //提交至数据库
        } catch (\Throwable $th) {
            DB::rollback(); //数据库回滚
            return LMReturn::Error('订单创建出现问题', 'EC009');
        }
        return LMReturn::Success($order, '新增订单成功');
    }

    /**
     * @description: 把订单状态转成对应的 状态
     * @param {*} $str
     * @return {*}
     */
    public function orderStrToStatus($str)
    {
        $un_zip_status = '2';
        $cash_status = '2';
        switch ($str) {
            case '草稿':
                $order_status = '-1';
                break;
            case '待付款':
                $order_status = '1';
                break;
            case '审核中':
                $order_status = '2';
                break;
            case '待生效':
                $order_status = '3';
                break;
            case '已生效':
                $order_status = '4';
                break;
            case '生效中':
                $order_status = '4';
                break;
            case '拒绝':
                $order_status = '5';
                break;
            case '待兑付':
                $order_status = '6';
                break;
            case '到期兑付已解押':
                $order_status = '7';
                $un_zip_status = '1';
                $cash_status = '1';
                break;
            case '提前解压已兑付':
                $order_status = '8';
                $un_zip_status = '1';
                $cash_status = '1';
                break;
            case '提前兑付已解押':
                $order_status = '8';
                $un_zip_status = '1';
                $cash_status = '1';
                break;
            case '到期兑付待解押':
                $order_status = '9';
                $un_zip_status = '2';
                $cash_status = '1';
                break;
            case '到期已兑付':
                $order_status = '9';
                $un_zip_status = '2';
                $cash_status = '1';
                break;
            case '到期解压已兑付':
                $order_status = '10';
                $un_zip_status = '1';
                $cash_status = '1';
                break;
            case '到期兑付已解押':
                $order_status = '10';
                $un_zip_status = '1';
                $cash_status = '1';
                break;
            case '提前兑付待解押':
                $order_status = '11';
                $un_zip_status = '2';
                $cash_status = '1';
                break;
            case '提前兑付':
                $order_status = '11';
                $un_zip_status = '2';
                $cash_status = '1';
                break;
            case '已解压':
                $order_status = '12';
                $un_zip_status = '1';
                $cash_status = '1';
                break;
            case '到期兑付未解压':
                $order_status = '13';
                $un_zip_status = '2';
                $cash_status = '1';
                break;
            case '社保订单已续期':
                $order_status = '14';
                break;
            default:
                $order_status = $str;
                break;
        }
        $order_data['order_status'] = $order_status;
        $order_data['un_zip_status'] = $un_zip_status;
        $order_data['cash_status'] = $cash_status;
        return $order_data;
    }
    /**
     * @description: 根据订单状态返回 兑付状态
     * @param {*} $str
     * @return {*}
     */

    /**
     * @description: 这是例子用到的处理单元格方法：
     * @param string $str
     * @return string
     */
    private function handleStr($str)
    {

        $str = trim($str); //清除字符串两边的空格

        $str = preg_replace("/\t/", "", $str); //使用正则表达式替换内容，如：空格，换行，并将替换为空。

        $str = preg_replace("/\r\n/", "", $str);

        $str = preg_replace("/\r/", "", $str);

        $str = preg_replace("/\n/", "", $str);

        $str = preg_replace("//", "", $str);

        $str = preg_replace("//", "", $str); //匹配html中的空格

        return trim($str); //返回字符串

    }


    /**
     * @description: 这是例子用到的处理单元格方法：
     * @param {*} $recordData
     * @return {*}
     */
    private function entrustExport($recordData)
    {
        $spreadsheet = new Spreadsheet();
        print($spreadsheet);
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', '排序');
        $sheet->setCellValue('B1', '分公司');
        $sheet->setCellValue('C1', '出借人');
        $sheet->setCellValue('D1', '出借金额（万）');
        $sheet->setCellValue('E1', '生效时间');
        $sheet->setCellValue('F1', '到期时间');
        $sheet->setCellValue('G1', '借款期限');
        $sheet->setCellValue('H1', '付息日期');
        $sheet->setCellValue('I1', '付息金额');
        $sheet->setCellValue('J1', '年化业绩');
        $sheet->setCellValue('K1', '借款人');
        $sheet->setCellValue('L1', '备注');
        $sheet->setCellValue('M1', '解押详情');
        $sheet->setCellValue('N1', '兑付日期');
        $sheet->setCellValue('O1', '实际年化');
        print($sheet);
        $i = 1;
        foreach ($recordData as $data) {
            $i++;
            $sheet->setCellValue('A' . $i, $data['A']);
            $sheet->setCellValue('B' . $i, $data['B']);
            $sheet->setCellValue('C' . $i, $data['C']);
            $sheet->setCellValue('D' . $i, $data['D']);
            $sheet->setCellValue('E' . $i, $data['E']);
            $sheet->setCellValue('F' . $i, $data['F']);
            $sheet->setCellValue('G' . $i, $data['G']);
            $sheet->setCellValue('H' . $i, $data['H']);
            $sheet->setCellValue('I' . $i, $data['I']);
            $sheet->setCellValue('J' . $i, $data['J']);
            $sheet->setCellValue('K' . $i, $data['K']);
            $sheet->setCellValue('L' . $i, $data['L']);
            $sheet->setCellValue('M' . $i, $data['M']);
            $sheet->setCellValue('N' . $i, $data['N']);
            $sheet->setCellValue('O' . $i, $data['O']);
        }

        //保存文件

        $fileName = 'Transaction-' . date('YmdHmi') . '.xlsx';
        $filePath = public_path('uploads/admin/recode/');

        $writer = new Xlsx($spreadsheet);

        $fileUrl = $filePath . $fileName;

        $writer->save($fileUrl);

        return $fileName;
    }
}
